Release 10.1A: OpenEdge Data Management:
SQL Development

Using the OpenEdge SQL Java classes

This section describes how you use the OpenEdge SQL Java classes to issue and process SQL statements in Java stored procedures.

To process SQL statements in a stored procedure, you must know whether the SQL statement generates output (in other words, if the statement is a query) or not. SELECT statements, for example, generate results: they retrieve data from one or more database tables and return the results as rows in a table.

Whether a statement generates such an SQL result set determines which OpenEdge SQL Java classes you should use to issue it:

In either case, if you want to return a result set to the application, use the DhSQLResultSet class to store rows of data in a procedure result set. You must use DhSQLResultSet methods to transfer data from an SQL result set to the procedure result set for the calling application to process it. You can also use DhSQLResultSet methods to store rows of data generated internally by the procedure.

In addition, OpenEdge SQL provides the DhSQLException class so procedures can process and generate Java exceptions through the try, catch, and throw constructs.

Passing values to SQL statements

Stored procedures must be able to pass and receive values from SQL statements they execute. They do this through the setParam and getValue methods.

setParam method: pass input values to SQL statements

The setParam method sets the value of an SQL statement’s parameter marker to the specified value (a literal value, a procedure variable, or a procedure input parameter).

The setParam method takes two arguments. This is the syntax for setParam:

Syntax 
setParam ( marker_num , value ) ;

marker_num

Specifies the ordinal number of the parameter marker in the SQL statement that is to receive the value as an integer. 1 denotes the first parameter marker, 2 denotes the second, n denotes the nth.

value

Specifies a literal, variable name, or input parameter that contains the value to be assigned to the parameter marker.

Example 9–9 shows a segment of a stored procedure that uses setParam to assign values from two procedure variables to the parameter markers in an SQL INSERT statement. When the procedure executes, it substitutes the value of the cust_number procedure variable for the first parameter marker and the value of the cust_name variable for the second parameter marker.

Example 9–9: Stored procedure using setParam 
SQLIStatement insert_cust = new SQLIStatement (
     "INSERT INTO customer VALUES (?,?) ");
     insert_cust.setParam (1, cust_number);
     insert_cust.setParam (2, cust_name);

getValue method: pass values from SQL result sets to variables

The getValue method of the SQLCursor class assigns a single value from an SQL result set (returned by an SQL query or another stored procedure) to a procedure variable or output parameter using the following syntax:

Syntax 
getValue ( col_num , sql_data_type ) ;

col_num

Specifies the desired column of the result set as integer. getValue retrieves the value in the currently fetched record of the column denoted by col_num. 1 denotes the first column of the result set, 2 denotes the second, n denotes the nth.

sql_data_type

Specifies the corresponding SQL data type.

This example shows how the getValue() method works. This method returns a Java object that must be cast to the corresponding SQL data type: 

cnum = (Integer) NEWROW.getValue(1, INTEGER); 
cnname = (String) NEWROW.getValue(1, CHARACTER);

Passing values to and from stored procedures: input and output parameters

Applications need to pass and receive values from the stored procedures they call. They do this through input and output parameters. When applications process the CREATE PROCEDURE statement, the SQL engine declares Java variables of the same name. Therefore, the stored procedure can refer to input and output parameters as if they were Java variables declared in the body of the stored procedure.

Procedure result sets are another way for applications to receive output values from a stored procedure. Procedure result sets provide output in a row-oriented tabular format.

Parameter declarations include the parameter type (IN, OUT, or INOUT), the parameter name, and SQL data type.

Declare input and output parameters in the specification section of a stored procedure, as shown in Example 9–10.

Example 9–10: Stored procedures input and output parameters 
CREATE PROCEDURE order_entry (
     IN  cust_name    CHAR(20),
     IN  item_num     INTEGER,
     IN  quantity     INTEGER,
     OUT status_code  INTEGER,
     INOUT order_num  INTEGER
)

When the order_entry stored procedures executes, the calling application passes values for the cust_name, item_num, quantity, and order_num input parameters. The body of the procedure refers to them as Java variables. Similarly, Java code in the body of order_entry processes and returns values in the status_code and order_num output parameters.

Implicit data type conversion between SQL and Java types

When the OpenEdge SQL Engine creates a stored procedure, it converts the type of any input and output parameters.

The java.lang package, part of the Java core classes, defines classes for all the primitive Java types that “wrap” values of the corresponding primitive type in an object. The OpenEdge SQL Engine converts the SQL data types declared for input and output parameters to one of these wrapper types, as shown in Table 9–2.

Be sure to use wrapper types when declaring procedure variables to use as arguments to the getValue, setParam, and set methods. These methods take objects as arguments and will generate compilation errors if you pass a primitive type to them.

The following example illustrates the use of the Java wrapper type Long for a SQL type INTEGER: 

CREATE PROCEDURE proc1(INOUT f1 char(50), INOUT f2 integer) 
BEGIN 
f1 = new String("new rising sun"); 
f2 = new Integer("999"); 
END 
CREATE PROCEDURE proc2() 
BEGIN 
String in1 = new String("String type"); 
String out1 = new String(); 
Long out2 = new Long("0"); 
SQLCursor call_proc = new SQLCursor("call proc1(?, ?)"); 
call_proc.setParam(1,in1); 
// In setParam you can use either String or String type 
// for SQL types CHAR, and VARCHAR 
call_proc.setParam(2,new Long("111")); 
call_proc.open(); 
out1 = (String)call_proc.getParam(1,CHAR); 
// getParam requires String type for CHAR 
out2 = (Long)call_proc.getParam(2,INTEGER); 
call_proc.close(); 
END

When the OpenEdge SQL Engine submits the Java class it creates from the stored procedure to the Java compiler, the compiler checks for data-type consistency between the converted parameters and variables you declare in the body of the stored procedure.

To avoid type mismatch errors, use the data-type mappings shown in Table 9–2 for declaring parameters and result-set fields in the procedure specification and the Java variables in the procedure body.

Table 9–2: Mapping between SQL and Java data types 
SQL type
Java methods
Java wrapper type
CHAR, VARCHAR
All
String
CHAR, VARCHAR
set, setParam
String
NUMERIC
All
java.math.BigDecimal
DECIMAL
All
java.math.BigDecimal
BIT
All
Boolean
TINYINT
All
Byte[1]
SMALLINT
All
Integer
INTEGER
All
Integer
REAL
All
Float
FLOAT
All
Double
DOUBLE PRECISION
All
Double
BINARY
All
Byte[ ]
VARBINARY
All
Byte[ ]
DATE
All
java.sql.Date
TIME
All
java.sql.Time
TIMESTAMP
All
java.sql.Timestamp

Executing an SQL statement

If an SQL statement does not generate a result set, stored procedures can execute the statement in one of two ways:

Table 9–3 shows the SQL statements that do not generate result sets. You can execute these statements in a stored procedure using either the SQLIStatement or the SQLPStatement class.

Table 9–3: Executable SQL statements 
ALTER USER 

CREATE INDEX 

CREATE PROCEDURE 

CREATE SYNONYM 

CREATE TABLE 

CREATE TRIGGER 

CREATE USER 

CREATE VIEW 

DELETE 

DROP INDEX 

DROP PROCEDURE 

DROP TABLE 

DROP TRIGGER 

DROP USER 

DROP VIEW 

GRANT  

INSERT 

REVOKE 

UPDATE 

UPDATE STATISTICS

Immediate execution

Use immediate execution when a procedure must execute an SQL statement only once.

This stored procedure in this sample script inserts a row in a table. The constructor for SQLIStatement takes the SQL INSERT statement as its only argument. In Example 9–11, the statement includes five parameter markers.

Example 9–11: Stored procedure using INSERT statement 
CREATE PROCEDURE insert_team(
     IN  empnum      INTEGER not null,
     IN  FirstName   VARCHAR(30) not null,
     IN  LastName    VARCHAR(50) not null,
     IN  State       VARCHAR(50) not null,
     IN  Sport       CHAR(20)
) 
 
BEGIN
     SQLIStatement insert_team = new SQLIStatement (
        "INSERT INTO team (empnum, FirstName, LastName, State, Sport)
          VALUES ( ?,?,?,?,? ) ");
     insert_team.setParam (1, empnum);
     insert_team.setParam (2, FirstName);
     insert_team.setParam (3, LastName);
     insert_team.setParam (4, State);
     insert_team.setParam (5, Sport) ;
     insert_team.execute ();
END

COMMIT WORK ;

Prepared execution

Use prepared execution when you must execute the same SQL statement repeatedly. Prepared execution avoids the overhead of creating multiple SQLIStatement objects for a single statement.

There is an advantage to prepared execution when you execute the same SQL statement from within a loop. Instead of creating an object with each iteration of the loop, prepared execution creates an object once and supplies input parameters for each execution of the statement.

Once a stored procedure creates an SQLPStatement object, you can execute the object multiple times, supplying different values for each execution.

Example 9–12 extends the previous example to use prepared execution.

Example 9–12: Stored procedure with prepared execution 
CREATE PROCEDURE prepared_insert_customer (
   IN  cust_number INTEGER,
   IN  cust_name   CHAR(20)
)
 
BEGIN
   SQLPStatement p_insert_cust = new SQLPStatement (
   "INSERT INTO customer VALUES (?,?) ");
   .
   .
   .
int i;
for (i = 0; i < new_custs.length; i++)
{
   p_insert_cust.setParam (1, new_custs[i].cust_number);
   p_insert_cust.setParam (2, new_custs[i].cust_name);
   p_insert_cust.execute ();
}
END

Retrieving data: the SQLCursor class

Methods of the SQLCursor class let stored procedures retrieve rows of data. When stored procedures create an object from the SQLCursor class, they pass as an argument an SQL statement that generates a result set. The SQL statement is either a SELECT or a CALL statement:

Either way, once the procedure creates an object from the SQLCursor class, the processing of result sets follows the same steps.

To process result sets:
  1. Open the cursor by using the SQLCursor.open method.

  2. Check whether there are any records in the result set by using the SQLCursor.found method.

  3. If there are records in the result set, loop through the result set:

    • Try to fetch a record by using the SQLCursor.fetch method.
    • Check whether the fetch returned a record with the SQLCursor.found method.
    • If the fetch operation returned a record, assign values from the result-set record’s fields to procedure variables or procedure output parameters by using the SQLCursor.getValue method.
    • Process the data.
    • If the fetch operation did not return a record, exit the loop.
  4. Close the cursor by using the SQLCursor.close method.

Example 9–13 uses SQLCursor to process the result set returned by an SQL SELECT statement.

Example 9–13: Stored procedure using SQLCursor 
CREATE PROCEDURE get_sal () 
IMPORT 
import java.math.*; 
BEGIN 
StringBuffer ename = new StringBuffer (20) ; 
BigDecimal esal = new BigDecimal (2) ; 
SQLCursor empcursor = new SQLCursor ( 
"SELECT name, sal FROM emp " ) ; 
empcursor.open () ; 
empcursor.fetch (); 
while (empcursor.found ()) 
{ 
ename = (StringBuffer) empcursor.getValue (1, CHAR); 
esal = (BigDecimal) empcursor.getValue (2, NUMERIC); 
// do something with the values here 
} 
empcursor.close () ; 
END

Stored procedures also use SQLCursor objects to process a result set returned by another stored procedure. Instead of a SELECT statement, the SQLCursor constructor includes a CALL statement that invokes the desired procedure.

Example 9–14 shows an excerpt from a stored procedure that processes the result set returned by another procedure, get_customers.

Example 9–14: Stored procedure processing results of another procedure 
SQLCursor cust_cursor = new SQLCursor (
   "CALL get_customers (?) ") ;
    cust_cursor.setParam (1, "NE");
    cust_cursor.open () ;
    for (;;)
    {
        cust_cursor.fetch ();
        if (cust_cursor.found ())
        {
            cust_number = (Integer) cust_cursor.getValue (1, INTEGER);
            cust_name = (String) cust_cursor.getValue (2, CHAR) ;
        }
        else
                break;
    }
    cust_cursor.close () ;

Returning a procedure result set to applications: the RESULT clause and DhSQLResultSet

The get_sal procedure in the previous example with a CREATE PROCEDURE uses the SQLCursor.getValue method to store the values of a database record in individual variables. The procedure did not, however, do anything with those values and they will be overwritten in the next iteration of the loop that fetches records.

The DhSQLResultSet class provides a way for a procedure to store rows of data in a procedure result set so that the rows can be returned to the calling application. There can only be one procedure result set in a stored procedure.

A stored procedure must explicitly process a result set to return it to the calling application:

When the SQL engine creates a Java class from a CREATE PROCEDURE statement that contains the RESULT clause, it implicitly instantiates an object of type DhSQLResultSet, and calls it SQLResultSet. Invoke methods of the SQLResultSet instance to populate fields and rows of the procedure result set.

The next example extends the get_sal procedure to return a procedure result set: 

CREATE PROCEDURE get_sal2 () 
RESULT ( 
empname CHAR(20), 
empsal NUMERIC 
) 
IMPORT 
import java.math.*; 
BEGIN 
StringBuffer ename = new StringBuffer (20) ; 
BigDecimal esal = new BigDecimal (2) ; 
SQLCursor empcursor = new SQLCursor ( 
"SELECT name, sal FROM emp " ) ; 
empcursor.open () ; 
do 
{ 
empcursor.fetch (); 
if (empcursor.found ()) 
{ 
ename = (StringBuffer) empcursor.getValue (1, CHAR); 
esal = (BigDecimal) empcursor.getValue (2, NUMERIC); 
// NUMERIC and DECIMAL are synonyms 
SQLResultSet.set (1, ename); 
SQLResultSet.set (2, esal); 
SQLResultSet.insert (); 
} 
} while (empcursor.found ()) ; 
empcursor.close () ; 
END

For each row of the SQL result set assigned to procedure variables, the procedure:

Handling null values

Stored procedures routinely must set and detect null values:

Setting SQL statement input parameters and procedure result set fields to null

Both the setParam method and set method take objects as their value arguments. You can pass a NULL reference directly to the method or pass a variable that has been assigned the null value.

Example 9–15 uses both techniques to set an SQL input parameter to NULL.

Example 9–15: Stored procedure setting input parameter to NULL 
CREATE TABLE t1 (
     c1 INTEGER,
     c2 INTEGER,
     c3 INTEGER);

CREATE PROCEDURE test_nulls( )
BEGIN
     Integer pvar_int ;
     pvar_int = null ;

     SQLIStatement insert_t1 = new SQLIStatement
     ( "INSERT INTO t1 (c1, c2, c3) values (?,?,?) ");
 
     // Set to non-null value
     insert_t1.setParam(1, new Integer(1));
 
     // Set directly to null
     insert_t1.setParam(2, null);
 
     // Set indirectly to null
     insert_t1.setParam(3, pvar_int);
 
     insert_t1.execute();
END

Assigning null values from SQL result sets: the SQLCursor.wasNULL method

If the value of the field argument to the SQLCursor.getValue method is NULL, the SQL engine returns a run-time error.

Example 9–16 illustrates the error returned when the argument to SQLCursor.getValue is NULL.

Example 9–16: Stored procedure error message 
(error(-20144): Null value fetched.)

This means you must always check whether a value is null before attempting to assign a value in an SQL result set to a procedure variable or output parameter. The SQLCursor class provides the wasNULL method for this purpose.

The SQLCursor.wasNULL method returns TRUE if a field in the result set is null. It takes a single integer argument that specifies which field of the current row of the result set to check.

Example 9–17 illustrates using the wasNULL method.

Example 9–17: Stored procedure using wasNULL method 
CREATE PROCEDURE test_nulls2( ) 
RESULT ( res_int1 INTEGER , 
res_int2 INTEGER , 
res_int3 INTEGER ) 
BEGIN 
Integer pvar_int1 = new Integer(0); 
Integer pvar_int2 = new Integer(0); 
Integer pvar_int3 = new Integer(0); 
SQLCursor select_t1 = new SQLCursor 
( "SELECT c1, c2, c3 from t1" ); 
select_t1.open(); 
select_t1.fetch(); 
while ( select_t1.found() ) 
{ 
// Assign values from the current row of the SQL result set 
// to the pvar_intx procedure variables. Must first check 
// whether the values fetched are null: if they are, must set 
// pvars explicitly to null. 
if ((select_t1.wasNULL(1)) == true) 
pvar_int1 = null; 
else 
pvar_int1 = (Integer) select_t1.getValue(1, INTEGER); 
if ((select_t1.wasNULL(2)) == true) 
pvar_int2 = null; 
else 
pvar_int2 = (Integer) select_t1.getValue(2, INTEGER); 
if ((select_t1.wasNULL(3)) == true) 
pvar_int3 = null; 
else 
pvar_int3 = (Integer) select_t1.getValue(3, INTEGER); 
// Transfer the value from the procedure variables to the 
// columns of the current row of the procedure result set. 
SQLResultSet.set(1,pvar_int1); 
SQLResultSet.set(2,pvar_int2); 
SQLResultSet.set(3,pvar_int3); 
// Insert the row into the procedure result set. 
SQLResultSet.insert(); 
select_t1.fetch(); 
} 
// Close the SQL result set. 
select_t1.close(); 
END

Handling errors

OpenEdge SQL stored procedures use standard Java try/catch constructs to process exceptions.

Any errors in SQL statement execution result in the creation of a DhSQLException class object. When OpenEdge SQL detects an error in an SQL statement, it throws an exception. The stored procedure should use try/catch constructs to process such exceptions. The getDiagnostics method of the DhSQLException class object provides a mechanism to retrieve different details of the error.

The getDiagnostics method takes a single argument whose value specifies which error message detail it returns. See Table 9–4 for explanations of the getDiagnostics error-handling options.

Table 9–4: getDiagnostics error-handling options 
Argument value
Returns 
RETURNED_SQLSTATE
The SQLSTATE returned by execution of the previous SQL statement. 
MESSAGE_TEXT
The condition indicated by RETURNED_SQLSTATE. 
CLASS_ORIGIN
Not currently used. Always returned null. 
SUBCLASS_ORIGIN
Not currently used. Always returned null.

Example 9–18 shows an excerpt from a stored procedure that uses DhSQLException.getDiagnostics.

Example 9–18: Stored procedure using DhSQLException.getDiagnostics 
try
{
     SQLIStatement insert_cust = new SQLIStatement (
     "INSERT INTO customer VALUES (1,2) ");
}
catch (DhSQLException e)
{
     errstate = e.getDiagnostics (DhSQLException.RETURNED_SQLSTATE) ;
     errmesg  = e.getDiagnostics (DhSQLException.MESSAGE_TEXT) ;
          .
          .
          .
}

Stored procedures can also throw their own exceptions by instantiating a DhSQLException object and throwing the object when the procedure detects an error in execution. The conditions under which the procedure throws the exception object are completely dependent on the procedure.

Example 9–19 illustrates using the DhSQLException constructor to create an exception object called excep. It then throws the excep object under all conditions.

Example 9–19: Stored procedure using DhSQLException constructor 
CREATE PROCEDURE sp1_02()
BEGIN
// raising exception 
     DhSQLException excep =
        new DhSQLException(777,new String("Entered the tst02 procedure"));
     if (true)
     throw excep;
END

Calling stored procedures from other stored procedures

Stored procedures and triggers can call other stored procedures. Nesting procedures lets you take advantage of existing procedures. Instead of rewriting the code, procedures can simply issue CALL statements to the existing procedures.

Another use for nesting procedures is for assembling result sets generated by queries on different databases into a single result set. With this technique, the stored procedure processes multiple SELECT statements through multiple instances of the SQLCursor class. For each of the instances, the procedure uses the DhSQLResultSet class to add rows to the result set returned by the procedure.

Stored procedure parameter requirements and usage

When one stored procedure is calling another stored procedure, the following requirements must be met for using the three parameter types in order to properly allocate the SQLDA structure to the correct size:

INOUT and OUT parameters when one Java stored procedure calls another

If an OUT or INOUT parameter is of data type CHARACTER, then getParam() returns a Java String Object. You must declare a procedure variable of type String, and explicitly cast the value returned by getParam to type String. Before calling getParam() you must call the SQLCursor.wasNULL method to test whether the returned value is null. If getParam() is called for a null value, it raises a DhSQLException.

The getParam() method returns the value of an INOUT or OUT parameter identified by the number you specify in the fieldIndex parameter. getParam() returns the value as an object of the data type you specify in the fieldType parameter. Since getParam() returns the result as an instance of class Object, you must explicitly cast your inout_var variable to the correct data type.

These are the general steps to follow when calling one Java stored procedure from another:

  1. Register OUT parameters in the calling stored procedure.

  2. Declare Java variables in the snippet of the calling procedure.

  3. Invoke the other stored procedure.

Example 9–20 illustrates the steps required for calling one Java stored procedure from another.

Example 9–20: Stored procedure calling another 
create procedure lotusp(
IN f1 char(50),
INOUT f2 char(50),
OUT f3 char(50)
)
RESULT(f4 char(50))
BEGIN
     f2 = new String("new rising sun");
     f3 = new String("new rising lotus");
     SQLResultSet.set(1, new String("the fog - the snow - the ice"));
     SQLResultSet.insert();
END
 
commit work;
 
create procedure proc1()
BEGIN
     String inout_param = new String("sun");
     String out_param = new String();
 
     SQLCursor call_proc = new SQLCursor("call lotusp(?,?,?)");
     call_proc.setParam(1, new String("moon"));
     call_proc.setParam(2, inout_param);
     call_proc.registerOutParam(3, CHAR);
     //  OR you can specify the optional scale parameter
     //  call_proc.registerOutParam(3, CHAR, 15);
     call_proc.open();
     inout_param = (String)call_proc.getParam(2, CHAR);
     out_param = (String)call_proc.getParam(3, CHAR);
     call_proc.close();
END

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095